package com;

import java.security.PrivateKey;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: BGONZ8
 * Date: 9/23/11
 * Time: 5:50 PM
 * To change this template use File | Settings | File Templates.
 */
public class DB {

    private String sql = "select BANNER from SYS.V_$VERSION";
    private Connection conn = null;
    private String result = "";
    private List<String> sqlStmts;

    public String getResult() {
        return result;
    }

    private String getSql() {
        String retval = "";
        for (String item : sqlStmts)
        {
            retval = retval + item + "\n";
        }
        return retval;
    }

    private List<String> getSqlStmts() {
        return sqlStmts;
    }

    private void setSql(String sql) {
        this.sql = sql;
    }

    public DB() throws Exception
    {
        init();
    }

    private void init() throws Exception
    {
        sqlStmts = new ArrayList<String>();
        Class.forName("oracle.jdbc.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521/XE";
        conn = DriverManager.getConnection(url,"ben","ben");
        conn.setAutoCommit(true);
    }

    public void close() throws Exception
    {
        if ( null != conn )
        {
            conn.close();
        }
    }

    public ResultSet execute(String query)  throws Exception
    {
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(query);
        conn.commit();
        return rset;
    }

    private Boolean validate() throws Exception
    {
        try
        {
            for (String sqlQuery : sqlStmts)
            {
                int ctr = 0;
                Statement stmt = conn.createStatement();
                ResultSet rset = stmt.executeQuery(sqlQuery);
                //Boolean first = rset.first();
                ResultSetMetaData rsmd = rset.getMetaData();
                int numColumns = rsmd.getColumnCount();

                result = result + "\n\n" + sqlQuery + "\n\n";
                while (rset.next() && ctr <= 10) {
                    for (int i=1; i<numColumns+1; i++) {
                        String columnName = rsmd.getColumnName(i);
                        result = result + "\t" + columnName + " = " + rset.getString(columnName) + "," ;
                    }
                    ctr++;
                    result = result + "\n\n";
                }
                stmt.close();
                if ( 0 == ctr )
                {
                    result = result + "\tRecord Not Found.\n\n";
                }
                else
                {
                    result = result + "\tRecord Found.\n\n";
                }
            }
            return true;
        }
        catch (Exception e)
        {
            System.out.println("ERROR: " + e.getMessage());
            throw e;
        }
    }

}
